﻿using aier.dataaccess.model;
using Dapper;
using log4net;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace aier.dataaccess.dapper
{
    public class NetDoctorsStatisticDal
    {
        public DapperHelper dapperHelper { get; set; }
        public ILog log { get; set; }


        /// <summary>
        /// 获取学术推广统计信息
        /// </summary>
        /// <param name="startdata">开始日间</param>
        /// <param name="enddata">结束日间</param>
        /// <param name="ownerid">负责人ID</param>
        /// <returns></returns>
        public NetDoctorsStatisticView QryNetDoctorsStatistic(string startdata, string enddata, Guid ownerid)
        {
            return dapperHelper.ExecueWithoutTransaction<NetDoctorsStatisticView>(a =>
            {
                string sql = $@"
                            select 
                            sum(co.JDL)JDL,
                            sum(co.KFL)KFL,
                            sum(co.BLZ_ZZL)BLZ_ZZL,
                            sum(co.ETYB_ZZL)ETYB_ZZL,
                            sum(co.JMJYB_ZZL)JMJYB_ZZL,
                            sum(co.JMSXJ_ZZL)JMSXJ_ZZL,
                            sum(co.JMYZ_ZZL)JMYZ_ZZL,
                            sum(co.LDB_ZZL)LDB_ZZL,
                            sum(co.QGY_ZZL)QGY_ZZL,
                            sum(co.QGSS_ZZL)QGSS_ZZL,
                            sum(co.XRS_ZZL)XRS_ZZL,
                            sum(co.YDB_ZZL)YDB_ZZL,
                            sum(co.YZXJYK_ZZL)YZXJYK_ZZL,
                            sum(co.YXYGPJ_ZZL)YXYGPJ_ZZL,
                            sum(co.QT_ZZL)QT_ZZL,
                            sum(co.GDJS_ZZL)GDJS_ZZL,
                            sum(co.VIP_ZZL)VIP_ZZL,
                            sum(co.BLZ_SSL)BLZ_SSL,
                            sum(co.ETYB_SSL)ETYB_SSL,
                            sum(co.JMJYB_SSL)JMJYB_SSL,
                            sum(co.JMSXJ_SSL)JMSXJ_SSL,
                            sum(co.JMYZ_SSL)JMYZ_SSL,
                            sum(co.LDB_SSL)LDB_SSL,
                            sum(co.QGY_SSL)QGY_SSL,
                            sum(co.QGSS_SSL)QGSS_SSL,
                            sum(co.XRS_SSL)XRS_SSL,
                            sum(co.YDB_SSL)YDB_SSL,
                            sum(co.YZXJYK_SSL)YZXJYK_SSL,
                            sum(co.YXYGPJ_SSL)YXYGPJ_SSL,
                            sum(co.QT_SSL)QT_SSL,
                            sum(co.GDJS_SSL)GDJS_SSL,
                            sum(co.VIP_SSL)VIP_SSL
                            from
                            (
	                            select 
	                            0 as BLZ_ZZL,
	                            0 as ETYB_ZZL,
	                            0 as JMJYB_ZZL,
	                            0 as JMSXJ_ZZL,
	                            0 as JMYZ_ZZL,
	                            0 as LDB_ZZL,
	                            0 as QGY_ZZL,
	                            0 as QGSS_ZZL,
	                            0 as XRS_ZZL,
	                            0 as YDB_ZZL,
	                            0 as YZXJYK_ZZL,
	                            0 as YXYGPJ_ZZL,
	                            0 as QT_ZZL,
                                0 as GDJS_ZZL,
                                0 as VIP_ZZL,
	                            0 as BLZ_SSL,
	                            0 as ETYB_SSL,
	                            0 as JMJYB_SSL,
	                            0 as JMSXJ_SSL,
	                            0 as JMYZ_SSL,
	                            0 as LDB_SSL,
	                            0 as QGY_SSL,
	                            0 as QGSS_SSL,
	                            0 as XRS_SSL,
	                            0 as YDB_SSL,
	                            0 as YZXJYK_SSL,
	                            0 as YXYGPJ_SSL,
	                            0 as QT_SSL,
                                0 as GDJS_SSL,
                                0 as VIP_SSL,
	                            sum(case when  (c.new_type = 1 or c.new_type=3 or c.new_type=4) then 1 else 0 end)JDL,
	                            sum(case when  (c.new_type = 1 or c.new_type=3 or c.new_type=4) and c.new_cooperativeintention =1 then 1 else 0 end)KFL
	                            from ContactBase c  with (nolock) 
                                where  (c.new_type = 1 or c.new_type=3 or c.new_type=4)
                                and c.StateCode=0
	                            and c.OwnerId ='{ownerid}'
	                            and convert(varchar(10), dbo.[fn_UTCToTzCodeSpecificLocalTime](c.CreatedOn,210),23)>='{startdata}'
	                            and convert(varchar(10), dbo.[fn_UTCToTzCodeSpecificLocalTime](c.CreatedOn,210),23)<='{enddata}'

	                            union 

	                            select 
	                            sum(case when r.new_content =100000000 then 1 else 0 end)BLZ_ZZL,   --白内障      __转诊量 
	                            sum(case when r.new_content =100000001 then 1 else 0 end)ETYB_ZZL,  --儿童眼病    __转诊量 
	                            sum(case when r.new_content =100000002 then 1 else 0 end)JMJYB_ZZL, --角膜及眼表  __转诊量 
	                            sum(case when r.new_content =100000003 then 1 else 0 end)JMSXJ_ZZL, --角膜塑型镜  __转诊量 
	                            sum(case when r.new_content =100000004 then 1 else 0 end)JMYZ_ZZL,  --角膜移植	  __转诊量 
	                            sum(case when r.new_content =100000005 then 1 else 0 end)LDB_ZZL,   --泪道病	  __转诊量 
	                            sum(case when r.new_content =100000006 then 1 else 0 end)QGY_ZZL,   --青光眼	  __转诊量 
	                            sum(case when r.new_content =100000007 then 1 else 0 end)QGSS_ZZL,  --屈光手术	  __转诊量 
	                            sum(case when r.new_content =100000008 then 1 else 0 end)XRS_ZZL,   --斜弱视	  __转诊量 
	                            sum(case when r.new_content =100000009 then 1 else 0 end)YDB_ZZL,   --眼底病      __转诊量 
	                            sum(case when r.new_content =100000010 then 1 else 0 end)YZXJYK_ZZL,--眼整形及眼眶__转诊量  
	                            sum(case when r.new_content =100000011 then 1 else 0 end)YXYGPJ_ZZL,--医学验光配镜__转诊量 
	                            sum(case when r.new_content =100000012 then 1 else 0 end)QT_ZZL,    --其他        __转诊量 
                                sum(case when r.new_content =100000013 then 1 else 0 end)GDJS_ZZL,  --高度近视        __转诊量 
                                sum(case when r.new_content =100000014 then 1 else 0 end)VIP_ZZL,   --VIP        __转诊量 
	                            0 as BLZ_SSL,   
	                            0 as ETYB_SSL,  
	                            0 as JMJYB_SSL, 
	                            0 as JMSXJ_SSL, 
	                            0 as JMYZ_SSL,  
	                            0 as LDB_SSL,   
	                            0 as QGY_SSL,   
	                            0 as QGSS_SSL,  
	                            0 as XRS_SSL,   
	                            0 as YDB_SSL,   
	                            0 as YZXJYK_SSL,
	                            0 as YXYGPJ_SSL,
	                            0 as QT_SSL,
	                            0 as GDJS_SSL,
	                            0 as VIP_SSL,
	                            0 as JDL,
	                            0 as KFL     
	                            from new_recommendBase r with(nolock)
	                            where 
	                            r.statecode =0
	                            and r.OwnerId='{ownerid}' 
	                            and convert(varchar(10), dbo.[fn_UTCToTzCodeSpecificLocalTime](r.new_appointmenttothehospitaldate,210),23)>='{startdata}'
	                            and convert(varchar(10), dbo.[fn_UTCToTzCodeSpecificLocalTime](r.new_appointmenttothehospitaldate,210),23)<='{enddata}'
		
	                            union  
		
	                            select 
	                            0 as BLZ_ZZL,   
	                            0 as ETYB_ZZL,  
	                            0 as JMJYB_ZZL, 
	                            0 as JMSXJ_ZZL, 
	                            0 as JMYZ_ZZL,  
	                            0 as LDB_ZZL,   
	                            0 as QGY_ZZL,   
	                            0 as QGSS_ZZL,  
	                            0 as XRS_ZZL,   
	                            0 as YDB_ZZL,   
	                            0 as YZXJYK_ZZL,
	                            0 as YXYGPJ_ZZL,
	                            0 as QT_ZZL,
                                0 as GDJS_ZZL,
                                0 as VIP_ZZL,
	                            sum(case when r.new_content =100000000 and l.new_tothehospital=1 and rs.new_istohospital=1 then 1 else 0 end)BLZ_SSL,    --白内障      __手术量 
	                            sum(case when r.new_content =100000001 and l.new_tothehospital=1 and rs.new_istohospital=1 then 1 else 0 end)ETYB_SSL,   --儿童眼病    __手术量 
	                            sum(case when r.new_content =100000002 and l.new_tothehospital=1 and rs.new_istohospital=1 then 1 else 0 end)JMJYB_SSL,  --角膜及眼表  __手术量 
	                            sum(case when r.new_content =100000003 and l.new_tothehospital=1 and rs.new_istohospital=1 then 1 else 0 end)JMSXJ_SSL, --角膜塑型镜  __手术量 
	                            sum(case when r.new_content =100000004 and l.new_tothehospital=1 and rs.new_istohospital=1 then 1 else 0 end)JMYZ_SSL,  --角膜移植	  __手术量 
	                            sum(case when r.new_content =100000005 and l.new_tothehospital=1 and rs.new_istohospital=1 then 1 else 0 end)LDB_SSL,   --泪道病	  __手术量 
	                            sum(case when r.new_content =100000006 and l.new_tothehospital=1 and rs.new_istohospital=1 then 1 else 0 end)QGY_SSL,   --青光眼	  __手术量 
	                            sum(case when r.new_content =100000007 and l.new_tothehospital=1 and rs.new_istohospital=1 then 1 else 0 end)QGSS_SSL,  --屈光手术	  __手术量 
	                            sum(case when r.new_content =100000008 and l.new_tothehospital=1 and rs.new_istohospital=1 then 1 else 0 end)XRS_SSL,   --斜弱视	  __手术量 
	                            sum(case when r.new_content =100000009 and l.new_tothehospital=1 and rs.new_istohospital=1 then 1 else 0 end)YDB_SSL,   --眼底病      __手术量 
	                            sum(case when r.new_content =100000010 and l.new_tothehospital=1 and rs.new_istohospital=1 then 1 else 0 end)YZXJYK_SSL,--眼整形及眼眶__手术量  
	                            sum(case when r.new_content =100000011 and l.new_tothehospital=1 and rs.new_istohospital=1 then 1 else 0 end)YXYGPJ_SSL,--医学验光配镜__手术量 
	                            sum(case when r.new_content =100000012 and l.new_tothehospital=1 and rs.new_istohospital=1 then 1 else 0 end)QT_SSL,    --其他        __手术量 
	                            sum(case when r.new_content =100000013 and l.new_tothehospital=1 and rs.new_istohospital=1 then 1 else 0 end)GDJS_SSL,  --高度近视__手术量 
	                            sum(case when r.new_content =100000014 and l.new_tothehospital=1 and rs.new_istohospital=1 then 1 else 0 end)VIP_SSL,   --VIP        __手术量 
	                            0 as JDL,
	                            0 as KFL    
	                            from new_recommendBase r with(nolock) 
	                            left join LeadBase l with(nolock) on l.LeadId = r.new_lead
	                            left join new_reservationBase rs with(nolock) on rs.new_lead = r.new_lead and rs.new_recommend = r.new_recommendId  
	                            where 
	                            r.statecode =0
	                            and l.StateCode =0 
	                            and l.new_tothehospital=1 
                                and rs.new_istohospital=1
	                            and r.OwnerId='{ownerid}' 
	                            and convert(varchar(10), dbo.[fn_UTCToTzCodeSpecificLocalTime](rs.new_hospitalizeddate,210),23)>='{startdata}'
	                            and convert(varchar(10), dbo.[fn_UTCToTzCodeSpecificLocalTime](rs.new_hospitalizeddate,210),23)<='{enddata}'
                            ) co";

                SqlBuilder sqlBuilder = new SqlBuilder();
                var sqlTemplate = sqlBuilder.AddTemplate(sql);
                return a.QueryFirstOrDefault<NetDoctorsStatisticView>(sqlTemplate.RawSql);
            });
        }

    }
}
